package com.xaicode.template.sqlite3.db;

import cn.hutool.core.util.StrUtil;
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Configuration;
import org.sqlite.SQLiteDataSource;

import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.Objects;

/**
 * @author xiachangjian <xiachangjian@sataero.cn>
 * @date 2023/2/15
 * @since v2.0.1
 */
@Slf4j
@Configuration
public class DBCheck extends DynamicRoutingDataSource {

    @Value("${spring.datasource.dynamic.datasource.master.url}")
    private String sysUrl;

    @Value("${spring.datasource.dynamic.datasource.running.url}")
    private String runningUrl;


    @Override
    public void afterPropertiesSet() throws Exception {

        checkSysTable();
        checkRunningTable();

        log.info("数据库检查完成，开始创建系统数据源...");
        super.afterPropertiesSet();
    }

    private void checkSysTable() {
        String logPre = "[系统库 sys]";
        log.info(logPre + " 开始检查...");

        String baseAirportSchema = "CREATE TABLE sys_airport (id varchar(32) not null, create_time datetime, update_time datetime, name varchar(32), code3 varchar(3), code4 varchar(4), primary key (id));";
        String initDataSql = "";
        doCheck(logPre, sysUrl, "sys_airport", baseAirportSchema, initDataSql);

        String basePlaneSchema = "CREATE TABLE sys_plane (id varchar(255) not null, create_time datetime, update_time datetime, name varchar(255), tail_no varchar(20), age integer, primary key (id));";
        initDataSql = "";
        doCheck(logPre, sysUrl, "sys_plane", basePlaneSchema, initDataSql);

        log.info(logPre + " 检查完成");
    }

    private void checkRunningTable() {
        String logPre = "[运行库 content]";
        log.info(logPre + " 开始检查...");

        String runningSchema = "CREATE TABLE sys_airport_plane (airport_id varchar(32) not null, plane_id varchar(32) not null);";
        String initDataSql = "";

        doCheck(logPre, runningUrl, "sys_airport_plane", runningSchema, initDataSql);
        log.info(logPre + " 检查完成");
    }

    // ----

    /**
     * 获取数据库连接
     *
     * @param url sqlite 数据库文件地址
     * @return .
     */
    private Connection getConnection(String url) {
        try {
            DataSourceBuilder<?> builder = DataSourceBuilder.create();
            builder.driverClassName("org.sqlite.JDBC");
            builder.url(url);
            builder.type(SQLiteDataSource.class);
            DataSource dataSource = builder.build();
            return dataSource.getConnection();
        } catch (Exception e) {
            log.error("创建数据库[{}]连接失败，原因：{}", url, e.toString());
            e.printStackTrace();
            return null;
        }
    }

    /**
     * 使用sql进行执行检查，若执行成功则说明表存在，否则表不存在。
     * 本方法仅适用于 SQLite3 数据库，其他情况另行讨论。
     * <p>
     * sql：select count(*) from table;
     *
     * @param connection .
     * @param tableName  .
     * @return true 表存在
     */
    private boolean tableExist(Connection connection, String tableName) {
        String countSql = "select count(*) from %s;";
        try {
            connection.prepareStatement(String.format(countSql, tableName)).execute();
            return true;
        } catch (Exception e) {
            log.error("检查表[{}]是否存在失败，原因：{}", tableName, e.toString());
            //e.printStackTrace();
            return false;
        }
    }

    private void doCheck(String logPre, String url, String checkTableName, String schema, String initDataSql) {
        Connection conn = getConnection(url);
        if (Objects.isNull(conn)) {
            log.error(logPre + " 获取数据库[{}]连接失败，检查中断", url);
            return;
        }

        try {
            conn.setAutoCommit(false);
            if (!tableExist(conn, checkTableName)) {
                log.info(logPre + " 表[{}]不存在，开始创建...", checkTableName);
                conn.prepareStatement(schema).execute();
                conn.commit();
                log.info(logPre + " 表[{}]创建成功", checkTableName);
                // 初始化数据
                if (StrUtil.isNotBlank(initDataSql)) {
                    String[] initDataSqls = initDataSql.split(";");
                    for (String sql : initDataSqls) {
                        conn.prepareStatement(sql).execute();
                        log.info(logPre + "库[{}]执行 [{}]", conn.getMetaData().getURL(), sql);
                    }
                    conn.commit();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

}
